	package util;

	import java.io.InputStream;
	import java.text.DecimalFormat;
	import java.text.ParseException;
	import java.text.SimpleDateFormat;
	import java.util.ArrayList;
	import java.util.Calendar;
	import java.util.Date;
	import java.util.HashSet;
	import java.util.List;

	import org.alex.samples.model.User;
	import org.apache.poi.hssf.usermodel.HSSFDateUtil;
	import org.apache.poi.hssf.usermodel.HSSFWorkbook;
	import org.apache.poi.poifs.filesystem.NotOLE2FileException;
	import org.apache.poi.ss.usermodel.Cell;
	import org.apache.poi.ss.usermodel.Row;
	import org.apache.poi.ss.usermodel.Sheet;
	import org.apache.poi.ss.usermodel.Workbook;
	import org.apache.poi.xssf.usermodel.XSSFWorkbook;

	public class ExcelUtils {
		public	static final String OFFICE_EXCEL_2007_POSTFIX = ".xlsx";
		public	static final String OFFICE_EXCEL_2003_POSTFIX = ".xls";
		public static Workbook openWorkbook(InputStream is, String postfix) {
			Workbook workbook = null;
			try{
				if(OFFICE_EXCEL_2003_POSTFIX.equals(postfix)){
					workbook = new HSSFWorkbook(is);
				}else if(OFFICE_EXCEL_2007_POSTFIX.equals(postfix)){
					workbook = new XSSFWorkbook(is);
				}
			}catch (Exception e) {
				if(e instanceof NotOLE2FileException){
					return null;
				}
				e.printStackTrace();
			}
			return workbook;
		}
		public static List<User> getStudentListByExcel(Workbook wk) throws ParseException {
			final String GENDER_MALE_STRING = "男",GENDER_FEMALE_STRING = "女";
			final Integer GENDER_MALE_INTEGER = 1,GERNDER_FEMALE_INTEGER = 0;
			List<User> returnList = new ArrayList<User>();
			Sheet sheet = wk.getSheetAt(0);
			int maxCellNumber = 0;
			int maxRowNumber = sheet.getLastRowNum();
			User user = null;
			for(int currentRowNumber = 1;currentRowNumber<= maxRowNumber;currentRowNumber++){
				Row row = sheet.getRow(currentRowNumber);
				maxCellNumber = row.getLastCellNum();
				user= new User();
				for(int currentCellNumber = 0;currentCellNumber<maxCellNumber;currentCellNumber++){
					Cell cell = row.getCell(currentCellNumber);
					switch (currentCellNumber) {
					case 0:
						user.setId(new Integer(getValue(cell)));
						break;
					case 1:
						user.setName((String)getValue(cell));
						break;
					case 2:
						String gender = (String)getValue(cell);
						if(GENDER_MALE_STRING.equals(gender)){
							user.setGender(GENDER_MALE_INTEGER);
						} else if(GENDER_FEMALE_STRING.equals(gender)){
							user.setGender(GERNDER_FEMALE_INTEGER);
						}
						break;
					case 3:
						user.setTel((String)getValue(cell));
						break;
					case 4:
						user.setEmail((String)getValue(cell));
						break;
					case 5:
						user.setAddress((String)getValue(cell));
						break;
					case 6:
						SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
						Date birhdaydate = sdf.parse((String)getValue(cell));
						user.setBirthday((Date)birhdaydate);
						break;
					case 7:
						String decodingPassword = (String)getValue(cell);
						user.setPassword(MD5Utils.getMD5Code(decodingPassword));
						break;
					default:
						break;
					}
				}
				user.setLogin_fail_count(0);
				Calendar now = Calendar.getInstance();
				user.setLast_lock_time(now.getTime());
				user.setExamUserSubject(new HashSet<>());
				returnList.add(user);
			}
			return returnList;
		}
		@SuppressWarnings({ "static-access" })
		private static String getValue(Cell cell){
			if (cell == null) {
				return "";
			}
			if (cell.getCellType() == cell.CELL_TYPE_BOOLEAN) {
				return String.valueOf(cell.getBooleanCellValue());
			} else if (cell.getCellType() == cell.CELL_TYPE_NUMERIC) {
				// 处理日期格式

				if (HSSFDateUtil.isCellDateFormatted(cell)) {
					SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
					return sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
				}
				DecimalFormat df = new DecimalFormat("0");

				return String.valueOf(df.format(cell.getNumericCellValue()));
			} else if (cell.getCellType() == cell.CELL_TYPE_FORMULA) {
				return String.valueOf(cell.getCellFormula());
			} else {
				return String.valueOf(cell.getStringCellValue());
			}

	}
	}
